{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.simplefilter('ignore')\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "pd.set_option('max_columns', None)\n",
    "pd.set_option('max_rows', 300)\n",
    "pd.set_option('max_colwidth', 200)\n",
    "\n",
    "%matplotlib inline\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "from tqdm import tqdm\n",
    "tqdm.pandas()\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "\n",
    "import gc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 用户表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_user = pd.read_csv('raw_data/train/train_user.csv')\n",
    "test_user = pd.read_csv('raw_data/test/test_user.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>phone_no_m</th>\n",
       "      <th>city_name</th>\n",
       "      <th>county_name</th>\n",
       "      <th>idcard_cnt</th>\n",
       "      <th>arpu_201908</th>\n",
       "      <th>arpu_201909</th>\n",
       "      <th>arpu_201910</th>\n",
       "      <th>arpu_201911</th>\n",
       "      <th>arpu_201912</th>\n",
       "      <th>arpu_202001</th>\n",
       "      <th>arpu_202002</th>\n",
       "      <th>arpu_202003</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>672ddbf02a5544d32e4ecc9433b1981bffe23bf912273a3a835f6cccb78b8ed7554e9ab0fbcd33d19eb6063ce00542dd223cc5cc83c68f07bcf933547b6776b1</td>\n",
       "      <td>绵阳</td>\n",
       "      <td>江油分公司</td>\n",
       "      <td>1</td>\n",
       "      <td>46.06</td>\n",
       "      <td>45.00</td>\n",
       "      <td>45.0</td>\n",
       "      <td>45.00</td>\n",
       "      <td>45.00</td>\n",
       "      <td>45.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>5e1272273e041e82cb275ae877710be98cdaf5b0a8f34de8d361f71d5268fa0851edffd3950e170df1e3846fcf90cc7cc8299be9139a2ac4b5c5e5121d832674</td>\n",
       "      <td>德阳</td>\n",
       "      <td>旌阳分公司</td>\n",
       "      <td>1</td>\n",
       "      <td>79.00</td>\n",
       "      <td>79.20</td>\n",
       "      <td>79.1</td>\n",
       "      <td>79.30</td>\n",
       "      <td>41.40</td>\n",
       "      <td>34.1</td>\n",
       "      <td>59.4</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>eaab3472ec87b076e69e6e8bb62b14341638fc63661a6c682d6add360a4332a8ad294d8470d64a73c6e53e8413f0ad93b9ea65afab717e58d312554f33553ee7</td>\n",
       "      <td>成都</td>\n",
       "      <td>金堂分公司</td>\n",
       "      <td>2</td>\n",
       "      <td>18.85</td>\n",
       "      <td>24.45</td>\n",
       "      <td>22.0</td>\n",
       "      <td>21.40</td>\n",
       "      <td>22.15</td>\n",
       "      <td>21.1</td>\n",
       "      <td>61.3</td>\n",
       "      <td>63.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0ce1bb415704178bf44e9c9b431a39b083a132c8e6d99f3627cd4520064b93ec66a72d085a67f0f40eebb9f901072965073e398f04c4ae500a1db4dfb13a972c</td>\n",
       "      <td>成都</td>\n",
       "      <td>高新分公司</td>\n",
       "      <td>2</td>\n",
       "      <td>19.20</td>\n",
       "      <td>29.65</td>\n",
       "      <td>130.4</td>\n",
       "      <td>173.12</td>\n",
       "      <td>19.00</td>\n",
       "      <td>19.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>23.2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>28b87f35f63f65096a53e3a4c97eaffd4a6c43ffa7e92d7706b20520aa11fff0f4ce06203f66758ec0f0e70780e4e258aae6aa1f23439e115528e08eda71ed20</td>\n",
       "      <td>德阳</td>\n",
       "      <td>旌阳分公司</td>\n",
       "      <td>1</td>\n",
       "      <td>50.00</td>\n",
       "      <td>50.00</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.00</td>\n",
       "      <td>50.00</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                                         phone_no_m  \\\n",
       "0  672ddbf02a5544d32e4ecc9433b1981bffe23bf912273a3a835f6cccb78b8ed7554e9ab0fbcd33d19eb6063ce00542dd223cc5cc83c68f07bcf933547b6776b1   \n",
       "1  5e1272273e041e82cb275ae877710be98cdaf5b0a8f34de8d361f71d5268fa0851edffd3950e170df1e3846fcf90cc7cc8299be9139a2ac4b5c5e5121d832674   \n",
       "2  eaab3472ec87b076e69e6e8bb62b14341638fc63661a6c682d6add360a4332a8ad294d8470d64a73c6e53e8413f0ad93b9ea65afab717e58d312554f33553ee7   \n",
       "3  0ce1bb415704178bf44e9c9b431a39b083a132c8e6d99f3627cd4520064b93ec66a72d085a67f0f40eebb9f901072965073e398f04c4ae500a1db4dfb13a972c   \n",
       "4  28b87f35f63f65096a53e3a4c97eaffd4a6c43ffa7e92d7706b20520aa11fff0f4ce06203f66758ec0f0e70780e4e258aae6aa1f23439e115528e08eda71ed20   \n",
       "\n",
       "  city_name county_name  idcard_cnt  arpu_201908  arpu_201909  arpu_201910  \\\n",
       "0        绵阳       江油分公司           1        46.06        45.00         45.0   \n",
       "1        德阳       旌阳分公司           1        79.00        79.20         79.1   \n",
       "2        成都       金堂分公司           2        18.85        24.45         22.0   \n",
       "3        成都       高新分公司           2        19.20        29.65        130.4   \n",
       "4        德阳       旌阳分公司           1        50.00        50.00         50.0   \n",
       "\n",
       "   arpu_201911  arpu_201912  arpu_202001  arpu_202002  arpu_202003  label  \n",
       "0        45.00        45.00         45.0         45.0         45.0      0  \n",
       "1        79.30        41.40         34.1         59.4         60.0      0  \n",
       "2        21.40        22.15         21.1         61.3         63.0      0  \n",
       "3       173.12        19.00         19.0         19.0         23.2      0  \n",
       "4        50.00        50.00         50.0         50.0         50.0      0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_user.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>phone_no_m</th>\n",
       "      <th>city_name</th>\n",
       "      <th>county_name</th>\n",
       "      <th>idcard_cnt</th>\n",
       "      <th>arpu_202004</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>22d522340df77e2252c1a4d92b4bcb00d515e36f3ec6bf94e017b4ffd67e26520af18637ad91bde69abd831cc36cdc0c5af5e57bc280f95f79efef7bfa9c6926</td>\n",
       "      <td>成都</td>\n",
       "      <td>金牛分公司</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>5220d4b8429bdba3971a7b46a088c6b8fa6710f40607598ac1219daab6071510b3f66f78637420574dbb2d5488d44de9d4360a644bbe4ec45aaa4b7513bdc886</td>\n",
       "      <td>天府新区</td>\n",
       "      <td>天府直属部门</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>7d19dd2b50ced56f03d23bf928cf34dc570a48525571a868ffdcdc53a865b7a41c386a6f1d6567dd4a710530b933308c77d1ef89f77b580d1983dadd3de162ea</td>\n",
       "      <td>成都</td>\n",
       "      <td>锦江分公司</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>134a4a591185c9d3788021896dcfc235e9e0a6a1e3f8a4fd3a9f9d9bf4a033e791b7199db980b0678020bdf689d719306a5c694ffa47b4ba2bec43ceda68dfc1</td>\n",
       "      <td>绵阳</td>\n",
       "      <td>江油分公司</td>\n",
       "      <td>1</td>\n",
       "      <td>138.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2356dcd6759d50455ddaeed03c838843558e9182d5962f8a4c81a9a178063c49ab988750f795bb6c5462aee5dace2ad9fd18dbb1a6619dbb1e771e9b0f192da9</td>\n",
       "      <td>成都</td>\n",
       "      <td>青羊分公司</td>\n",
       "      <td>2</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                                         phone_no_m  \\\n",
       "0  22d522340df77e2252c1a4d92b4bcb00d515e36f3ec6bf94e017b4ffd67e26520af18637ad91bde69abd831cc36cdc0c5af5e57bc280f95f79efef7bfa9c6926   \n",
       "1  5220d4b8429bdba3971a7b46a088c6b8fa6710f40607598ac1219daab6071510b3f66f78637420574dbb2d5488d44de9d4360a644bbe4ec45aaa4b7513bdc886   \n",
       "2  7d19dd2b50ced56f03d23bf928cf34dc570a48525571a868ffdcdc53a865b7a41c386a6f1d6567dd4a710530b933308c77d1ef89f77b580d1983dadd3de162ea   \n",
       "3  134a4a591185c9d3788021896dcfc235e9e0a6a1e3f8a4fd3a9f9d9bf4a033e791b7199db980b0678020bdf689d719306a5c694ffa47b4ba2bec43ceda68dfc1   \n",
       "4  2356dcd6759d50455ddaeed03c838843558e9182d5962f8a4c81a9a178063c49ab988750f795bb6c5462aee5dace2ad9fd18dbb1a6619dbb1e771e9b0f192da9   \n",
       "\n",
       "  city_name county_name  idcard_cnt  arpu_202004  \n",
       "0        成都       金牛分公司           1          9.0  \n",
       "1      天府新区      天府直属部门           1          NaN  \n",
       "2        成都       锦江分公司           1          9.0  \n",
       "3        绵阳       江油分公司           1        138.7  \n",
       "4        成都       青羊分公司           2         39.0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_user.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "set()"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tr_u = set(list(train_user.phone_no_m.unique()))\n",
    "te_u = set(list(test_user.phone_no_m.unique()))\n",
    "\n",
    "tr_u & te_u"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAPUAAADnCAYAAADGrxD1AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjEsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy8QZhcZAAATsElEQVR4nO3de7Qe873H8fdvP/uxRZLGJYfmiBql7peoFqUXdWmdDnqoUK1TJ+XUKq27tcZR7Wi1BuXQutQtbXE4TZe7OTilQVkNIUIiSFpGQiIRkScXue85f8xYdmNfnr33M/Od+c33tdazsh6R5/fB/vjNM5ffz8RxjFLKHm3SAZRSraWlVsoyWmqlLKOlVsoyWmqlLKOlVsoyWmqlLKOlVsoyWmqlLKOlVsoyWmqlLKOlVsoyWmqlLKOlVsoyWmqlLKOlVsoyWmqlLKOlVsoyWmqlLKOlVsoyWmqlLKOlVsoyWmqlLKOlVsoyWmqlLKOlVsoyWmqlLNMuHUC1nuOFNWBb4BPAKODj6WsUsDkwHNgI2BDYgOTnwAArgfeBFemv7wOLgbe6ec2JAnddbv9QqmlGN8grN8cLPwnsBewC7Jy+PkVS1iytBF4BpgHT019fjAL3rYzHVX3QUjfJGHMocBVQA26K4zjIO4Pjhe3AnsD+XV6j8s7Rh9nAEx+8osB9VThP5Wipm2CMqQEzgUOAN4HJwHFxHM/IemzHC0cCLnBEOv7wrMdssfnAI8C9wINR4C4TzmM9LXUTjDGfA/w4jr+avj8PII7ji7MYz/HCbYCxwOHA50iODmywEngUuBu4Lwrcd4TzWElPlDVnS2BOl/dvAvu0cgDHC4cCRwPjgC+SnLiyzYYkRx0usM7xwoeAm4H7o8BdK5rMIlrq5nRXsJYc4jheuB9wInAMMKwVn1kSNT4s+HzHC38P3BwF7kzZWOWnh99NaPXht+OFbcBRwDm0eMa3wJ+AS6LAfVQ6SFlpqZtgjGknOVF2EMk12snAt+I4fqk/n+N44RCSw+uzSK4jq549C1wC3BUFbqd0mDLRUjfJGPM14EqSw8bxcRz/vNk/63hhB3AqcB4wMpuE1poJXAzcqje7NEdLnaH0MPt44KfA1sJxym4G8J9R4N4rHaTotNQZcbzwUJLDx92ls1jmceCsKHCnSAcpKi11izle6AC/Bg4TjmKzTuC3wLlR4L4nHaZotNQtkt7CeQ7wY2CIcJyqmA+cFgXuBOkgRaKlbgHHC/cCbgLGSGepqPuAU/RhkoSWehDSE2Hnk8zOeiOPrCUk37Vvlg4iTUs9QI4XbgncBhwgHEX9ozuAk6PAXSodRIqWegAcLzwCGA9sJp1FdetvwLFVPUOupe6H9GTYL4HTpbOoPq0mOTv+K+kgedNSN8nxwo2BPwIHS2dR/XIrcFIUuKulg+RFS90Exwu3B+4HtpfOogbkL8CRUeC+Kx0kD7qaaB8cLzwYmIQWusy+AExyvHAH6SB50FL3wvHCbwMPAptIZ1GDth3wV8cLD5AOkjUtdQ8cL/wecAt6/dkmmwAPpvflW0tL3Q3HC88Erkf//dhoQ+AexwsPlw6SFf2hXY/jhT8GrpDOoTLVAdzpeOE3pINkQUvdheOFFwAXSudQuagDf3C88DjpIK2ml7RSjhd+H7hWOofK3VrgiChwH5QO0ipaasDxwmOB29Ejl6paDhwYBe4z0kFaofKldrzwEOABst97ShXbQmB/G5YornSpHS8cQ3K3UZXW21Y9i4D9osCdJx1kMCpb6nSPqmfRBQHVP5oMfCEK3FXSQQaqkt8h06etJqCFVh/1WeBq6RCDUclSkzw++WXpEKqwTnK88CTpEANVucNvxwv/jeT2T6V6s4rkMHyydJD+qlSp00conwc2ks6iSmEOMCYK3EXSQfqjMoff6ffo29BCq+ZtRQlvSKpMqYELSE6CKNUfx6Y3J5VGJQ6/HS/cF3iSZHM7pfprEbBrWa5fWz9TO164Eck6VVpoNVCbkmzWUArWl5pksf3tpEOo0vua44XjpEM0w+rD7/Rs9zT0vm7VGguBHYp+Ntz2mfpqtNCqdUYCv5AO0RdrZ2rHC8eS3AqqVCt1AntFgTtVOkhPrJyp05NjuiSRykIbcJV0iN5YWWrgDGC0dAhlrS86XnikdIieWHf47XjhJsBrwMbSWZTVpgF7RIFbuALZOFOfixZaZW83oJCrkVo1U6cLH7yOrmSi8jEd2L1os7VtM/XZaKFVfnYFxkqHWJ81M7XjhcOAN4ER0llUpbwE7Fak2dqmmfoEtNAqf7tQsD3LrSi144UGOE06h6qsH0oH6MqKUgOHovtHKzmu44WOdIgP2FLq06UDqEprA06RDvGB0p8oc7xwG+DvgJHOoiptETA6CtwV0kFsmKm/hRZaydsUOEI6BNhR6m9LB1Aq9U3pAFDyw2/HCz8NPCedQ6nUKmCLKHAbkiHKPlMfLx1AqS46APGnt0pb6vTadCEOd5Tq4jjpAKUtNfBpYJR0CKXWc5DjhZtKBihzqf9FOoBS3aghfNtomUt9qHQApXpwiOTgpSy144UbA/tK51CqB1rqATgE3XFDFdfWjhfuIDV4WUt9oHQApfrwFamBy1rqfaQDKNWHz0sNXLpSO144hGTRN6WK7DNSA5eu1CTXp9ulQyjVh0+my1Xnroyl1kNvVRYis3UZS/1Z6QBKNWkviUHLWOpdpAMo1SQtdV/Shzh0A3lVFiLr5pWq1MCWwBDpEEo16ZMSg5at1J+SDqBUPwxzvHDzvAfVUiuVrW3zHrBspRY5nFFqEHL/me31Jg5jzFG9/X4cx3e1Nk6ftsh5PKUGa+u8B+zrzqzDe/m9GMi71CNzHk+pwcp9FZReSx3H8bi8gjRpM+kASvVT7reKNvWd2hizhTHmZmPMg+n7nY0xJ2YbrVs6U6uyKWapgd8BDwP/nL6fCZyRRaA+aKlV2eR++N1sqUfGcTwB6ASI43gtsC6zVD3T/adV2RR2pl5ujNmM5OQYxph9AYldCMp2CU6pYXkP2OxzyWcB9wHbGmOeAv4JODqzVN1wvFDXJFNllPvPbVOljuN4ijHmS8AOJDtMvhrH8ZpMk32UllqVUTFLbYzZkGRT7c+THIL/xRjzmziOV2YZbj1a6owc1vbX535Vv3ob6Rw26sQshvdyHbPZw+9bgKXAr9P3xwG3AmOzCNUD/T6dkYvq4+ttJhbdKsZWbcSL8x6z2VLvEMfxHl3eTzTGvJBFoF5InG233oFtU17Y2Czfo++/Uw3Q6rwHbHb2ez494w2AMWYf4KlsInUvCtyVaLFbLqjfuFY6g+XyPvfU5wMd00i+Q9eB7xhjZqfvtwZmZB/vI5YDHxMY10p7m5dnbG4aIkvuVEixSg0clkuK5jXQUrfM5fXrlkpnqIBVeQ/Y1wMdb3R9b4zZHNgw00S9ew/YSnB8a+xmXps12izcWzpHBczJe8BmH+g4whgzC3gdeByIgAczzNWTRQJjWumK+rULjMFI56iAKO8Bmz1R9jOSrWNnxnG8DXAQOZ8oS80TGNM625q33tjOzNWtgPPxet4DNlvqNXEcvwu0GWPa4jieCIzJMFdPIoExrXNl/ZrZxujNPDmJ8h6w2evUi40xw4AngP82xiwAJC6FRAJjWmW0eWfuribSWTo/hZ2pvw6sAM4EHgL+Tu9LHWUlEhjTKpfXr5tlDHXpHBURU9SZOo7j5V3e/j6jLM2IBMcuvZEsfmdv84qe8c7P2/iNYl3SMsYsJX2Gev3fAuI4jvO+ZjybZKEGvQ98AC6t3/CSMRwgnaNCcj/0hj7KEcfx8DiOP9bNa7hAoT+4VXRW3uPaYATLFn+5barePZav4pW6oJ6XDlBGF9XHTzWG4dI5KiaSGFRLXQFDWbHMbZukT2Llb6bEoGUs9RTpAGXzo/bbnmsz+S+Ap3hCYtAyllpn6n7oYPXKY2qP7SSdo4Ii/EYkMXDpSh0F7rsInYAoo7PbJzxTM3Hu26kq/iw1cOlKnRL7F1YmNdat/W7tId0pVIaWup8elQ5QBqfW7pnUbjpHS+eoKC11Pz1K9zfFqJShs/MH7fduKZ2jol7Bb4g9UVjKUkeBuwCYLp2jyMbVHp60gVmry/7KEP16WMpSp/QQvEdxfG77H3QzQTla6gG6XzpAUY2tPT55iFm9vXSOioqBiZIBylzqx4H50iGK6Mfttw6VzlBhL+A3RJfdKm2po8BdB9wlnaNoDm17espws2IX6RwVJv4zWdpSp/4oHaBofl4fr4sJylkHjJcOUfZS6yF4F59vmzZtM7N0T+kcFfa/+I23pEOUutRR4HYCf5DOURSX1a/PcxdS9VE3SAeAkpc6db10gCLY08x6dZRZ9FnpHBX2JjJr4X9E6UsdBe4MhB5xK5Ir6tfqRgeyxuM3CrGBY+lLnbpOOoCkHc3s1xwzfx/pHBXWCdwkHeIDtpT6LmCBdAgp/1W/Zp4x1vy3LKOH8Bu575nVEyt+EKLAXU2B/k+ZJ8fMm7OjmaOztKwbpQN0ZUWpU78i2XCgUq6oX/e6MU3vtKJaby7wgHSIrqwpdRS48ynIJYW8jOLdt/c0f9NZWta1+A2JLah6ZE2pU5cisMm3lMvq179qDB3SOSpsAXCVdIj1WVXqKHDnAjdL58jDJixZtH/b9M9I56i4X+A3lkmHWJ9VpU4FwGrpEFkL6je9aAz6NJac2cBvpEN0x7pSR4E7B7hGOkeWhvH+kq+0Pav3eMu6UGLzu2ZYV+rUT4GF0iGycmH991OMYYR0jgqbjuzur72ystRR4C4GLpDOkYUhrHr/yLYnd5XOUXGnF+WW0O5YWerUjcCL0iFazWu/Y3KbiXX9MTl34zcKve68taVOV0Y5QzpHK9VZu/r42iO69piclcDZ0iH6Ym2pAaLAnQjcJp2jVU5vv/PpmukcJZ2jwi7HbxR+yyerS506Awse9mijc93JtQe2ls5RYS8CF0mHaIb1pU431DtFOsdgfa/2wKS6WfcJ6RwV9T5wLH6jFCvLWF9qgChw7wRul84xcHF8RvudH5dOUWGn4TdekQ7RrEqUOvUDQHxRuIE4vvbI0xuaNdtK56io/8FvlOrW48qUOgrc94BvAoV6oqYZ57XfrjeayHgNOFk6RH9VptQAUeA+CXjSOfrj621PPTvUrNpJOkcFrQGOw28skQ7SX5UqNUAUuJcDd0rnaNaF9d9tIJ2hon6E33hGOsRAVK7Uqe8Cs6RD9OXAtikvbGyW7y6do4L+D7hMOsRAVbLUUeAuAY4Clkpn6U1Qv7F03/8tMA/4Dn4jlg4yUJUsNUAUuNOBb5B8dyqcvc3LMzY3jb2kc1TMe8BX8Rul3sqpsqUGiAL3T8BJ0jm6c3n9ukIfRVhoGXAofmOadJDBqnSpAaLAvYWCPaa5m3lt1mizcG/pHBWyEji8rCfG1lf5UgNEgXsRBdqT64r6tQuMQbekzcca4Gj8xmPSQVpFS/2hU4DfSofY1rz1xnZm7r7SOSqiEzgevxFKB2klLXUq3Rb3RIRXI72yfs1sY6hJZqiIGPgP/MYE6SCtpqXuIgrc5D+00DYqo807c3c1kc7S+TgTvzFeOkQWtNTrSYt9MgLLv15ev26WMdTzHreCLsBvFG4R/lYxcVzaa+yZc7zQB36Sx1gjWfzO5I5ThhnDkDzGq6hVJIfct0oHyZLO1L2IAtcHTiCHG1Qurd/wkhY6U/OAL9leaNCZuimOFx5I8hDIxll8/giWLZ7a8b2aMQzP4vMVzwL/it8o5fP0/aUzdROiwP0zsD/J87Utd1F9/FQtdGbuAL5QlUKDztT94njhCJJr2Ue26jOHsmLZtI4T17QZNmnVZyoguWR1Pn7jYukgedNSD4DjhWcCl8Dgz1Rf3H7j48e1T/zS4FOpLpaS3FRyn3QQCVrqAXK8cF9gArDVQD+jg9UrZ3SMW1Iz8eatS1Z5M4Gj8BsvSQeRot+pBygK3EnAGAaxWcDZ7ROe0UK3TCfwS2BMlQsNOlO3hOOFh5HcrLJls3+mxrq1r3ac8Ha76RydXbLKeBkYh994WjpIEehM3QJR4D4A7AI0fdvhqbV7JmmhB20NcDGwpxb6QzpTt5jjhQcBV5GUvFuGzs5XO/79jQ3M2m3yS2adR4Ef4jdelg5SNDpTt1gUuI8CewDfB97p7u8ZV3t4khZ6wN4k2QLnYC1093SmzlB6Xft84DSgI/mrcfxyx7hZQ8xq3ZK2f5YAVwO/wG8slw5TZFrqHDhe6JCU+4Sxtceev6x+gy5V1Ly3gSuB3+A3Gs3+IWPMeOAwYEEcx7tmFa6ItNQ5crzQeWSDc07arm3uWaAPb/RhFsna27fgN1b19w8bY75IspjgLVpqlT1/xEjgVJJN+0YKpymaySR3692N3+gczAcZYxzgAS21yo8/YgjJo50/oJez5RXxEHApfmNiqz5QS61k+SN2BsYCxwA7C6fJywvA3cAf8RszWv3hWmpVHPYWPAYmAXcBd+E3MnmU9QNaalVM5S/4WmAiyYx8D35jXl4Da6lV8fkjdgT2A/ZKX7tTvLPoK4DpwFTgSeB+/MZ7eYcwxtwBHEByInI+8JM4jkWXf86LlrrM/BHtwE58WPJPkzw5tlFOCeaRfC+e2uXXWfiNdTmNr7qhpbaNP6IG7AjsRjJLbdrLaxOgfb1PiEn2lloBLAYWrveaC7wITMVvdHsbrJKlpa46f8THgKF8WORVZd6bWWmplbKOPqWllGW01EpZRkutlGW01EpZRkutlGW01EpZRkutlGW01EpZRkutlGW01EpZRkutlGW01EpZRkutlGW01EpZRkutlGW01EpZRkutlGW01EpZRkutlGW01EpZRkutlGW01EpZRkutlGW01EpZRkutlGW01EpZRkutlGX+H84NPwFrb/hXAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "_ = train_user.label.value_counts().plot.pie()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((6106, 13), (2045, 5))"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_user.shape, test_user.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>phone_no_m</th>\n",
       "      <th>city_name</th>\n",
       "      <th>county_name</th>\n",
       "      <th>idcard_cnt</th>\n",
       "      <th>arpu_201908</th>\n",
       "      <th>arpu_201909</th>\n",
       "      <th>arpu_201910</th>\n",
       "      <th>arpu_201911</th>\n",
       "      <th>arpu_201912</th>\n",
       "      <th>arpu_202001</th>\n",
       "      <th>arpu_202002</th>\n",
       "      <th>arpu_202003</th>\n",
       "      <th>label</th>\n",
       "      <th>arpu_202004</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>672ddbf02a5544d32e4ecc9433b1981bffe23bf912273a3a835f6cccb78b8ed7554e9ab0fbcd33d19eb6063ce00542dd223cc5cc83c68f07bcf933547b6776b1</td>\n",
       "      <td>绵阳</td>\n",
       "      <td>江油分公司</td>\n",
       "      <td>1</td>\n",
       "      <td>46.06</td>\n",
       "      <td>45.00</td>\n",
       "      <td>45.0</td>\n",
       "      <td>45.00</td>\n",
       "      <td>45.00</td>\n",
       "      <td>45.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>0</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>5e1272273e041e82cb275ae877710be98cdaf5b0a8f34de8d361f71d5268fa0851edffd3950e170df1e3846fcf90cc7cc8299be9139a2ac4b5c5e5121d832674</td>\n",
       "      <td>德阳</td>\n",
       "      <td>旌阳分公司</td>\n",
       "      <td>1</td>\n",
       "      <td>79.00</td>\n",
       "      <td>79.20</td>\n",
       "      <td>79.1</td>\n",
       "      <td>79.30</td>\n",
       "      <td>41.40</td>\n",
       "      <td>34.1</td>\n",
       "      <td>59.4</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>eaab3472ec87b076e69e6e8bb62b14341638fc63661a6c682d6add360a4332a8ad294d8470d64a73c6e53e8413f0ad93b9ea65afab717e58d312554f33553ee7</td>\n",
       "      <td>成都</td>\n",
       "      <td>金堂分公司</td>\n",
       "      <td>2</td>\n",
       "      <td>18.85</td>\n",
       "      <td>24.45</td>\n",
       "      <td>22.0</td>\n",
       "      <td>21.40</td>\n",
       "      <td>22.15</td>\n",
       "      <td>21.1</td>\n",
       "      <td>61.3</td>\n",
       "      <td>63.0</td>\n",
       "      <td>0</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0ce1bb415704178bf44e9c9b431a39b083a132c8e6d99f3627cd4520064b93ec66a72d085a67f0f40eebb9f901072965073e398f04c4ae500a1db4dfb13a972c</td>\n",
       "      <td>成都</td>\n",
       "      <td>高新分公司</td>\n",
       "      <td>2</td>\n",
       "      <td>19.20</td>\n",
       "      <td>29.65</td>\n",
       "      <td>130.4</td>\n",
       "      <td>173.12</td>\n",
       "      <td>19.00</td>\n",
       "      <td>19.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>23.2</td>\n",
       "      <td>0</td>\n",
       "      <td>23.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>28b87f35f63f65096a53e3a4c97eaffd4a6c43ffa7e92d7706b20520aa11fff0f4ce06203f66758ec0f0e70780e4e258aae6aa1f23439e115528e08eda71ed20</td>\n",
       "      <td>德阳</td>\n",
       "      <td>旌阳分公司</td>\n",
       "      <td>1</td>\n",
       "      <td>50.00</td>\n",
       "      <td>50.00</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.00</td>\n",
       "      <td>50.00</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                                         phone_no_m  \\\n",
       "0  672ddbf02a5544d32e4ecc9433b1981bffe23bf912273a3a835f6cccb78b8ed7554e9ab0fbcd33d19eb6063ce00542dd223cc5cc83c68f07bcf933547b6776b1   \n",
       "1  5e1272273e041e82cb275ae877710be98cdaf5b0a8f34de8d361f71d5268fa0851edffd3950e170df1e3846fcf90cc7cc8299be9139a2ac4b5c5e5121d832674   \n",
       "2  eaab3472ec87b076e69e6e8bb62b14341638fc63661a6c682d6add360a4332a8ad294d8470d64a73c6e53e8413f0ad93b9ea65afab717e58d312554f33553ee7   \n",
       "3  0ce1bb415704178bf44e9c9b431a39b083a132c8e6d99f3627cd4520064b93ec66a72d085a67f0f40eebb9f901072965073e398f04c4ae500a1db4dfb13a972c   \n",
       "4  28b87f35f63f65096a53e3a4c97eaffd4a6c43ffa7e92d7706b20520aa11fff0f4ce06203f66758ec0f0e70780e4e258aae6aa1f23439e115528e08eda71ed20   \n",
       "\n",
       "  city_name county_name  idcard_cnt  arpu_201908  arpu_201909  arpu_201910  \\\n",
       "0        绵阳       江油分公司           1        46.06        45.00         45.0   \n",
       "1        德阳       旌阳分公司           1        79.00        79.20         79.1   \n",
       "2        成都       金堂分公司           2        18.85        24.45         22.0   \n",
       "3        成都       高新分公司           2        19.20        29.65        130.4   \n",
       "4        德阳       旌阳分公司           1        50.00        50.00         50.0   \n",
       "\n",
       "   arpu_201911  arpu_201912  arpu_202001  arpu_202002  arpu_202003  label  \\\n",
       "0        45.00        45.00         45.0         45.0         45.0      0   \n",
       "1        79.30        41.40         34.1         59.4         60.0      0   \n",
       "2        21.40        22.15         21.1         61.3         63.0      0   \n",
       "3       173.12        19.00         19.0         19.0         23.2      0   \n",
       "4        50.00        50.00         50.0         50.0         50.0      0   \n",
       "\n",
       "   arpu_202004  \n",
       "0         45.0  \n",
       "1         60.0  \n",
       "2         63.0  \n",
       "3         23.2  \n",
       "4         50.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 只取训练集中最后一个月的数据\n",
    "\n",
    "train_user['arpu_202004'] = train_user['arpu_202003']\n",
    "train_user.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_user.drop(['arpu_201908', 'arpu_201909', 'arpu_201910', 'arpu_201911',\n",
    "                 'arpu_201912', 'arpu_202001', 'arpu_202002', 'arpu_202003'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 标签编码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 2/2 [00:00<00:00, 208.86it/s]\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>arpu_202004</th>\n",
       "      <th>city_name</th>\n",
       "      <th>county_name</th>\n",
       "      <th>idcard_cnt</th>\n",
       "      <th>label</th>\n",
       "      <th>phone_no_m</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>16</td>\n",
       "      <td>97</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>672ddbf02a5544d32e4ecc9433b1981bffe23bf912273a3a835f6cccb78b8ed7554e9ab0fbcd33d19eb6063ce00542dd223cc5cc83c68f07bcf933547b6776b1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>60.0</td>\n",
       "      <td>10</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5e1272273e041e82cb275ae877710be98cdaf5b0a8f34de8d361f71d5268fa0851edffd3950e170df1e3846fcf90cc7cc8299be9139a2ac4b5c5e5121d832674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>63.0</td>\n",
       "      <td>11</td>\n",
       "      <td>160</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>eaab3472ec87b076e69e6e8bb62b14341638fc63661a6c682d6add360a4332a8ad294d8470d64a73c6e53e8413f0ad93b9ea65afab717e58d312554f33553ee7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>23.2</td>\n",
       "      <td>11</td>\n",
       "      <td>181</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0ce1bb415704178bf44e9c9b431a39b083a132c8e6d99f3627cd4520064b93ec66a72d085a67f0f40eebb9f901072965073e398f04c4ae500a1db4dfb13a972c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>50.0</td>\n",
       "      <td>10</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28b87f35f63f65096a53e3a4c97eaffd4a6c43ffa7e92d7706b20520aa11fff0f4ce06203f66758ec0f0e70780e4e258aae6aa1f23439e115528e08eda71ed20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   arpu_202004  city_name  county_name  idcard_cnt  label  \\\n",
       "0         45.0         16           97           1    0.0   \n",
       "1         60.0         10           82           1    0.0   \n",
       "2         63.0         11          160           2    0.0   \n",
       "3         23.2         11          181           2    0.0   \n",
       "4         50.0         10           82           1    0.0   \n",
       "\n",
       "                                                                                                                         phone_no_m  \n",
       "0  672ddbf02a5544d32e4ecc9433b1981bffe23bf912273a3a835f6cccb78b8ed7554e9ab0fbcd33d19eb6063ce00542dd223cc5cc83c68f07bcf933547b6776b1  \n",
       "1  5e1272273e041e82cb275ae877710be98cdaf5b0a8f34de8d361f71d5268fa0851edffd3950e170df1e3846fcf90cc7cc8299be9139a2ac4b5c5e5121d832674  \n",
       "2  eaab3472ec87b076e69e6e8bb62b14341638fc63661a6c682d6add360a4332a8ad294d8470d64a73c6e53e8413f0ad93b9ea65afab717e58d312554f33553ee7  \n",
       "3  0ce1bb415704178bf44e9c9b431a39b083a132c8e6d99f3627cd4520064b93ec66a72d085a67f0f40eebb9f901072965073e398f04c4ae500a1db4dfb13a972c  \n",
       "4  28b87f35f63f65096a53e3a4c97eaffd4a6c43ffa7e92d7706b20520aa11fff0f4ce06203f66758ec0f0e70780e4e258aae6aa1f23439e115528e08eda71ed20  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_user = pd.concat([train_user, test_user])\n",
    "\n",
    "for f in tqdm(['city_name', 'county_name']):\n",
    "    lbl = LabelEncoder()\n",
    "    df_user[f] = df_user[f].fillna('NA')\n",
    "    df_user[f] = lbl.fit_transform(df_user[f].astype(str))\n",
    "    \n",
    "df_user.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 通话表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_voc = pd.read_csv('raw_data/train/train_voc.csv')\n",
    "test_voc = pd.read_csv('raw_data/test/test_voc.csv')\n",
    "\n",
    "train_voc['start_datetime'] = train_voc['start_datetime'].astype('datetime64')\n",
    "test_voc['start_datetime'] = test_voc['start_datetime'].astype('datetime64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 同样只取最后一个月的数据\n",
    "\n",
    "train_voc = train_voc[train_voc['start_datetime'] >= '2020-03-01 00:00:00']\n",
    "train_voc = train_voc.reset_index(drop=True)\n",
    "\n",
    "df_voc = pd.concat([train_voc, test_voc])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 时间切割\n",
    "\n",
    "df_voc['voc_day'] = df_voc['start_datetime'].dt.day\n",
    "df_voc['voc_hour'] = df_voc['start_datetime'].dt.hour\n",
    "df_voc['voc_ts'] = pd.to_timedelta(df_voc['start_datetime'], unit='ns').dt.total_seconds().astype(int)\n",
    "\n",
    "df_voc = df_voc.sort_values(by='voc_ts').reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 一天内通话统计量\n",
    "\n",
    "df_voc['voc_day_count'] = df_voc.groupby(['phone_no_m', 'voc_day'])['phone_no_m'].transform('count')\n",
    "df_voc['voc_day_count_max'] = df_voc.groupby('phone_no_m')['voc_day_count'].transform('max')\n",
    "df_voc['voc_day_count_min'] = df_voc.groupby('phone_no_m')['voc_day_count'].transform('min')\n",
    "df_voc['voc_day_count_mean'] = df_voc.groupby('phone_no_m')['voc_day_count'].transform('mean')\n",
    "df_voc['voc_day_count_std'] = df_voc.groupby('phone_no_m')['voc_day_count'].transform('std')\n",
    "\n",
    "del df_voc['voc_day_count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 一小时内通话统计量\n",
    "\n",
    "df_voc['voc_hour_count'] = df_voc.groupby(['phone_no_m', 'voc_hour'])['phone_no_m'].transform('count')\n",
    "df_voc['voc_hour_count_max'] = df_voc.groupby('phone_no_m')['voc_hour_count'].transform('max')\n",
    "df_voc['voc_hour_count_min'] = df_voc.groupby('phone_no_m')['voc_hour_count'].transform('min')\n",
    "df_voc['voc_hour_count_mean'] = df_voc.groupby('phone_no_m')['voc_hour_count'].transform('mean')\n",
    "df_voc['voc_hour_count_std'] = df_voc.groupby('phone_no_m')['voc_hour_count'].transform('std')\n",
    "\n",
    "del df_voc['voc_hour_count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 总量\n",
    "\n",
    "df_voc['voc_count'] = df_voc.groupby('phone_no_m')['phone_no_m'].transform('count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>phone_no_m</th>\n",
       "      <th>opposite_no_m</th>\n",
       "      <th>calltype_id</th>\n",
       "      <th>start_datetime</th>\n",
       "      <th>call_dur</th>\n",
       "      <th>city_name</th>\n",
       "      <th>county_name</th>\n",
       "      <th>imei_m</th>\n",
       "      <th>voc_day</th>\n",
       "      <th>voc_hour</th>\n",
       "      <th>voc_ts</th>\n",
       "      <th>voc_day_count_max</th>\n",
       "      <th>voc_day_count_min</th>\n",
       "      <th>voc_day_count_mean</th>\n",
       "      <th>voc_day_count_std</th>\n",
       "      <th>voc_hour_count_max</th>\n",
       "      <th>voc_hour_count_min</th>\n",
       "      <th>voc_hour_count_mean</th>\n",
       "      <th>voc_hour_count_std</th>\n",
       "      <th>voc_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1143237c02511244c4c9d8b1ca77c19f79b805a8577f3e62a84c8bc4359c930497a2363986cc3421912ee4589fb079ccbc3be3c330a6dd22b594a3433c9b414a</td>\n",
       "      <td>d7196a9499c13ce27d4c70ccc1576ebef915caf602a1ce2cb0b7defd20c7a246423cb2c3508418e930fa31242787bcc1428c4e18f2fd81b796fe629615825ec3</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-03-01 00:00:03</td>\n",
       "      <td>69</td>\n",
       "      <td>雅安</td>\n",
       "      <td>天全县</td>\n",
       "      <td>2a00a56a28c548e519e2e406bfdf7e2d6871207167a74378cbb43682aedbeb36e77c1db898be68683b4c9a028f87cb49ad8f827f248944c2a1ee839674d7aaae</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1583020803</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>13.351562</td>\n",
       "      <td>8.482129</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>20.914062</td>\n",
       "      <td>8.505156</td>\n",
       "      <td>256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>23e7b33b67d844031bcb1b6d4d4323786d0ff72276d682bd39b71217fed16278a7e8895feb0f96552553cef0018df935b9171b2e68cc92761b420dcdb6575c82</td>\n",
       "      <td>f40eb0b09ad309078f33940d5b5cddbf87bd3dd9433f6d780b88777b0c98babfe410cdd771af79cb0a0335fca1f5d1211fa1743654fb08675b3c675b269a642a</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-03-01 00:01:02</td>\n",
       "      <td>21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2437a08877d9e9dfe2decdc144952e20cbda3ff17902ba0a85cced4d76c75c16d40d5804f99364bea9e7f00654721c8bbb2a8bccbb951f6f2c996a5396b5289b</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1583020862</td>\n",
       "      <td>44</td>\n",
       "      <td>1</td>\n",
       "      <td>18.729730</td>\n",
       "      <td>11.004060</td>\n",
       "      <td>46</td>\n",
       "      <td>1</td>\n",
       "      <td>28.803440</td>\n",
       "      <td>12.130883</td>\n",
       "      <td>407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>333623561fc36866d7afcd9574c1572ecd7c9c68b9e372cd9c784baf0d9f4a3978c0ca98f57f912b0289f245046e305ac81755126d9df92883e7103859eb851c</td>\n",
       "      <td>a41fbfe0210cfa0305d678111a03a2049fc5e91073cc8a46d2ac46eb5a535b8cca8d19d2bc50435adcdc3f6de4de84ec15a216c9a1cc6d2e84f099fe05a20bce</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-03-01 00:02:25</td>\n",
       "      <td>103</td>\n",
       "      <td>成都</td>\n",
       "      <td>武侯区</td>\n",
       "      <td>127fd4d72ea49c9428766adb8e68f0dbb822668b319f20f1b39a1f914746d73d9b0acfb4cec0fee16fd42661090eb991ad7ba41200acbd7af216f12174fbb74d</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1583020945</td>\n",
       "      <td>72</td>\n",
       "      <td>1</td>\n",
       "      <td>33.888889</td>\n",
       "      <td>23.026947</td>\n",
       "      <td>38</td>\n",
       "      <td>1</td>\n",
       "      <td>27.940171</td>\n",
       "      <td>8.852869</td>\n",
       "      <td>468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>333623561fc36866d7afcd9574c1572ecd7c9c68b9e372cd9c784baf0d9f4a3978c0ca98f57f912b0289f245046e305ac81755126d9df92883e7103859eb851c</td>\n",
       "      <td>a41fbfe0210cfa0305d678111a03a2049fc5e91073cc8a46d2ac46eb5a535b8cca8d19d2bc50435adcdc3f6de4de84ec15a216c9a1cc6d2e84f099fe05a20bce</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-03-01 00:02:25</td>\n",
       "      <td>103</td>\n",
       "      <td>成都</td>\n",
       "      <td>武侯区</td>\n",
       "      <td>127fd4d72ea49c9428766adb8e68f0dbb822668b319f20f1b39a1f914746d73d9b0acfb4cec0fee16fd42661090eb991ad7ba41200acbd7af216f12174fbb74d</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1583020945</td>\n",
       "      <td>72</td>\n",
       "      <td>1</td>\n",
       "      <td>33.888889</td>\n",
       "      <td>23.026947</td>\n",
       "      <td>38</td>\n",
       "      <td>1</td>\n",
       "      <td>27.940171</td>\n",
       "      <td>8.852869</td>\n",
       "      <td>468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>23e7b33b67d844031bcb1b6d4d4323786d0ff72276d682bd39b71217fed16278a7e8895feb0f96552553cef0018df935b9171b2e68cc92761b420dcdb6575c82</td>\n",
       "      <td>f40eb0b09ad309078f33940d5b5cddbf87bd3dd9433f6d780b88777b0c98babfe410cdd771af79cb0a0335fca1f5d1211fa1743654fb08675b3c675b269a642a</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-03-01 00:03:15</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2437a08877d9e9dfe2decdc144952e20cbda3ff17902ba0a85cced4d76c75c16d40d5804f99364bea9e7f00654721c8bbb2a8bccbb951f6f2c996a5396b5289b</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1583020995</td>\n",
       "      <td>44</td>\n",
       "      <td>1</td>\n",
       "      <td>18.729730</td>\n",
       "      <td>11.004060</td>\n",
       "      <td>46</td>\n",
       "      <td>1</td>\n",
       "      <td>28.803440</td>\n",
       "      <td>12.130883</td>\n",
       "      <td>407</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                                         phone_no_m  \\\n",
       "0  1143237c02511244c4c9d8b1ca77c19f79b805a8577f3e62a84c8bc4359c930497a2363986cc3421912ee4589fb079ccbc3be3c330a6dd22b594a3433c9b414a   \n",
       "1  23e7b33b67d844031bcb1b6d4d4323786d0ff72276d682bd39b71217fed16278a7e8895feb0f96552553cef0018df935b9171b2e68cc92761b420dcdb6575c82   \n",
       "2  333623561fc36866d7afcd9574c1572ecd7c9c68b9e372cd9c784baf0d9f4a3978c0ca98f57f912b0289f245046e305ac81755126d9df92883e7103859eb851c   \n",
       "3  333623561fc36866d7afcd9574c1572ecd7c9c68b9e372cd9c784baf0d9f4a3978c0ca98f57f912b0289f245046e305ac81755126d9df92883e7103859eb851c   \n",
       "4  23e7b33b67d844031bcb1b6d4d4323786d0ff72276d682bd39b71217fed16278a7e8895feb0f96552553cef0018df935b9171b2e68cc92761b420dcdb6575c82   \n",
       "\n",
       "                                                                                                                      opposite_no_m  \\\n",
       "0  d7196a9499c13ce27d4c70ccc1576ebef915caf602a1ce2cb0b7defd20c7a246423cb2c3508418e930fa31242787bcc1428c4e18f2fd81b796fe629615825ec3   \n",
       "1  f40eb0b09ad309078f33940d5b5cddbf87bd3dd9433f6d780b88777b0c98babfe410cdd771af79cb0a0335fca1f5d1211fa1743654fb08675b3c675b269a642a   \n",
       "2  a41fbfe0210cfa0305d678111a03a2049fc5e91073cc8a46d2ac46eb5a535b8cca8d19d2bc50435adcdc3f6de4de84ec15a216c9a1cc6d2e84f099fe05a20bce   \n",
       "3  a41fbfe0210cfa0305d678111a03a2049fc5e91073cc8a46d2ac46eb5a535b8cca8d19d2bc50435adcdc3f6de4de84ec15a216c9a1cc6d2e84f099fe05a20bce   \n",
       "4  f40eb0b09ad309078f33940d5b5cddbf87bd3dd9433f6d780b88777b0c98babfe410cdd771af79cb0a0335fca1f5d1211fa1743654fb08675b3c675b269a642a   \n",
       "\n",
       "   calltype_id      start_datetime  call_dur city_name county_name  \\\n",
       "0            1 2020-03-01 00:00:03        69        雅安         天全县   \n",
       "1            1 2020-03-01 00:01:02        21       NaN         NaN   \n",
       "2            1 2020-03-01 00:02:25       103        成都         武侯区   \n",
       "3            1 2020-03-01 00:02:25       103        成都         武侯区   \n",
       "4            1 2020-03-01 00:03:15         1       NaN         NaN   \n",
       "\n",
       "                                                                                                                             imei_m  \\\n",
       "0  2a00a56a28c548e519e2e406bfdf7e2d6871207167a74378cbb43682aedbeb36e77c1db898be68683b4c9a028f87cb49ad8f827f248944c2a1ee839674d7aaae   \n",
       "1  2437a08877d9e9dfe2decdc144952e20cbda3ff17902ba0a85cced4d76c75c16d40d5804f99364bea9e7f00654721c8bbb2a8bccbb951f6f2c996a5396b5289b   \n",
       "2  127fd4d72ea49c9428766adb8e68f0dbb822668b319f20f1b39a1f914746d73d9b0acfb4cec0fee16fd42661090eb991ad7ba41200acbd7af216f12174fbb74d   \n",
       "3  127fd4d72ea49c9428766adb8e68f0dbb822668b319f20f1b39a1f914746d73d9b0acfb4cec0fee16fd42661090eb991ad7ba41200acbd7af216f12174fbb74d   \n",
       "4  2437a08877d9e9dfe2decdc144952e20cbda3ff17902ba0a85cced4d76c75c16d40d5804f99364bea9e7f00654721c8bbb2a8bccbb951f6f2c996a5396b5289b   \n",
       "\n",
       "   voc_day  voc_hour      voc_ts  voc_day_count_max  voc_day_count_min  \\\n",
       "0        1         0  1583020803                 32                  1   \n",
       "1        1         0  1583020862                 44                  1   \n",
       "2        1         0  1583020945                 72                  1   \n",
       "3        1         0  1583020945                 72                  1   \n",
       "4        1         0  1583020995                 44                  1   \n",
       "\n",
       "   voc_day_count_mean  voc_day_count_std  voc_hour_count_max  \\\n",
       "0           13.351562           8.482129                  33   \n",
       "1           18.729730          11.004060                  46   \n",
       "2           33.888889          23.026947                  38   \n",
       "3           33.888889          23.026947                  38   \n",
       "4           18.729730          11.004060                  46   \n",
       "\n",
       "   voc_hour_count_min  voc_hour_count_mean  voc_hour_count_std  voc_count  \n",
       "0                   1            20.914062            8.505156        256  \n",
       "1                   1            28.803440           12.130883        407  \n",
       "2                   1            27.940171            8.852869        468  \n",
       "3                   1            27.940171            8.852869        468  \n",
       "4                   1            28.803440           12.130883        407  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_voc.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 短信表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_sms = pd.read_csv('raw_data/train/train_sms.csv')\n",
    "test_sms = pd.read_csv('raw_data/test/test_sms.csv')\n",
    "\n",
    "train_sms['request_datetime'] = train_sms['request_datetime'].astype('datetime64')\n",
    "test_sms['request_datetime'] = test_sms['request_datetime'].astype('datetime64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 同样只取最后一个月的数据\n",
    "\n",
    "train_sms = train_sms[train_sms['request_datetime'] >= '2020-03-01 00:00:00']\n",
    "train_sms = train_sms.reset_index(drop=True)\n",
    "\n",
    "df_sms = pd.concat([train_sms, test_sms])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 时间切割\n",
    "\n",
    "df_sms['sms_day'] = df_sms['request_datetime'].dt.day\n",
    "df_sms['sms_hour'] = df_sms['request_datetime'].dt.hour\n",
    "df_sms['sms_ts'] = pd.to_timedelta(df_sms['request_datetime'], unit='ns').dt.total_seconds().astype(int)\n",
    "\n",
    "df_sms = df_sms.sort_values(by='sms_ts').reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 一天内短信统计量\n",
    "\n",
    "df_sms['sms_day_count'] = df_sms.groupby(['phone_no_m', 'sms_day'])['phone_no_m'].transform('count')\n",
    "df_sms['sms_day_count_max'] = df_sms.groupby('phone_no_m')['sms_day_count'].transform('max')\n",
    "df_sms['sms_day_count_min'] = df_sms.groupby('phone_no_m')['sms_day_count'].transform('min')\n",
    "df_sms['sms_day_count_mean'] = df_sms.groupby('phone_no_m')['sms_day_count'].transform('mean')\n",
    "df_sms['sms_day_count_std'] = df_sms.groupby('phone_no_m')['sms_day_count'].transform('std')\n",
    "\n",
    "del df_sms['sms_day_count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 一小时内短信统计量\n",
    "\n",
    "df_sms['sms_hour_count'] = df_sms.groupby(['phone_no_m', 'sms_hour'])['phone_no_m'].transform('count')\n",
    "df_sms['sms_hour_count_max'] = df_sms.groupby('phone_no_m')['sms_hour_count'].transform('max')\n",
    "df_sms['sms_hour_count_min'] = df_sms.groupby('phone_no_m')['sms_hour_count'].transform('min')\n",
    "df_sms['sms_hour_count_mean'] = df_sms.groupby('phone_no_m')['sms_hour_count'].transform('mean')\n",
    "df_sms['sms_hour_count_std'] = df_sms.groupby('phone_no_m')['sms_hour_count'].transform('std')\n",
    "\n",
    "del df_sms['sms_hour_count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 总量\n",
    "\n",
    "df_sms['sms_count'] = df_sms.groupby('phone_no_m')['phone_no_m'].transform('count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>phone_no_m</th>\n",
       "      <th>opposite_no_m</th>\n",
       "      <th>calltype_id</th>\n",
       "      <th>request_datetime</th>\n",
       "      <th>sms_day</th>\n",
       "      <th>sms_hour</th>\n",
       "      <th>sms_ts</th>\n",
       "      <th>sms_day_count_max</th>\n",
       "      <th>sms_day_count_min</th>\n",
       "      <th>sms_day_count_mean</th>\n",
       "      <th>sms_day_count_std</th>\n",
       "      <th>sms_hour_count_max</th>\n",
       "      <th>sms_hour_count_min</th>\n",
       "      <th>sms_hour_count_mean</th>\n",
       "      <th>sms_hour_count_std</th>\n",
       "      <th>sms_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>05b14594c2dfe7cecbf8b8e87220e92b8e7d7cfd81a9483262eea5d3347a314a154ab2902a7becb8b7657ef6171a9a6c095f2529e17eaced79c1b3f87ffb9dff</td>\n",
       "      <td>df22edbc0e3dd6bc4f2f453e687b743e8442a54834b64f06ee093a91c4a41385d46579107a443eee3ec0ce2339bcea4152a996ca8cf0b2cc846eb2acc9cc241b</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-03-01 00:00:27</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1583020827</td>\n",
       "      <td>74</td>\n",
       "      <td>8</td>\n",
       "      <td>41.424424</td>\n",
       "      <td>16.974697</td>\n",
       "      <td>126</td>\n",
       "      <td>1</td>\n",
       "      <td>63.134134</td>\n",
       "      <td>30.640949</td>\n",
       "      <td>999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>05b14594c2dfe7cecbf8b8e87220e92b8e7d7cfd81a9483262eea5d3347a314a154ab2902a7becb8b7657ef6171a9a6c095f2529e17eaced79c1b3f87ffb9dff</td>\n",
       "      <td>df22edbc0e3dd6bc4f2f453e687b743e8442a54834b64f06ee093a91c4a41385d46579107a443eee3ec0ce2339bcea4152a996ca8cf0b2cc846eb2acc9cc241b</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-03-01 00:00:27</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1583020827</td>\n",
       "      <td>74</td>\n",
       "      <td>8</td>\n",
       "      <td>41.424424</td>\n",
       "      <td>16.974697</td>\n",
       "      <td>126</td>\n",
       "      <td>1</td>\n",
       "      <td>63.134134</td>\n",
       "      <td>30.640949</td>\n",
       "      <td>999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>279b5823890d1af8035dadb33f6e8471fdc2832e907d1aa00c5f5bf47a7fbf451868c557233c679aa3fad7ba068010c059491a3c9029136a3e14e800cf47239c</td>\n",
       "      <td>d73a454ed717461675c5a243fa7bab9892640643b4a0855fabf86231ec6d274df9511cc4aa305bfc80592d3c6b0d20446d7f1fd7e2ac0f0436f57c409469fd2a</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-03-01 00:00:55</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1583020855</td>\n",
       "      <td>182</td>\n",
       "      <td>1</td>\n",
       "      <td>78.667954</td>\n",
       "      <td>77.305629</td>\n",
       "      <td>106</td>\n",
       "      <td>1</td>\n",
       "      <td>49.250965</td>\n",
       "      <td>33.940652</td>\n",
       "      <td>518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>279b5823890d1af8035dadb33f6e8471fdc2832e907d1aa00c5f5bf47a7fbf451868c557233c679aa3fad7ba068010c059491a3c9029136a3e14e800cf47239c</td>\n",
       "      <td>d73a454ed717461675c5a243fa7bab9892640643b4a0855fabf86231ec6d274df9511cc4aa305bfc80592d3c6b0d20446d7f1fd7e2ac0f0436f57c409469fd2a</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-03-01 00:00:55</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1583020855</td>\n",
       "      <td>182</td>\n",
       "      <td>1</td>\n",
       "      <td>78.667954</td>\n",
       "      <td>77.305629</td>\n",
       "      <td>106</td>\n",
       "      <td>1</td>\n",
       "      <td>49.250965</td>\n",
       "      <td>33.940652</td>\n",
       "      <td>518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>279b5823890d1af8035dadb33f6e8471fdc2832e907d1aa00c5f5bf47a7fbf451868c557233c679aa3fad7ba068010c059491a3c9029136a3e14e800cf47239c</td>\n",
       "      <td>d73a454ed717461675c5a243fa7bab9892640643b4a0855fabf86231ec6d274df9511cc4aa305bfc80592d3c6b0d20446d7f1fd7e2ac0f0436f57c409469fd2a</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-03-01 00:01:20</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1583020880</td>\n",
       "      <td>182</td>\n",
       "      <td>1</td>\n",
       "      <td>78.667954</td>\n",
       "      <td>77.305629</td>\n",
       "      <td>106</td>\n",
       "      <td>1</td>\n",
       "      <td>49.250965</td>\n",
       "      <td>33.940652</td>\n",
       "      <td>518</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                                         phone_no_m  \\\n",
       "0  05b14594c2dfe7cecbf8b8e87220e92b8e7d7cfd81a9483262eea5d3347a314a154ab2902a7becb8b7657ef6171a9a6c095f2529e17eaced79c1b3f87ffb9dff   \n",
       "1  05b14594c2dfe7cecbf8b8e87220e92b8e7d7cfd81a9483262eea5d3347a314a154ab2902a7becb8b7657ef6171a9a6c095f2529e17eaced79c1b3f87ffb9dff   \n",
       "2  279b5823890d1af8035dadb33f6e8471fdc2832e907d1aa00c5f5bf47a7fbf451868c557233c679aa3fad7ba068010c059491a3c9029136a3e14e800cf47239c   \n",
       "3  279b5823890d1af8035dadb33f6e8471fdc2832e907d1aa00c5f5bf47a7fbf451868c557233c679aa3fad7ba068010c059491a3c9029136a3e14e800cf47239c   \n",
       "4  279b5823890d1af8035dadb33f6e8471fdc2832e907d1aa00c5f5bf47a7fbf451868c557233c679aa3fad7ba068010c059491a3c9029136a3e14e800cf47239c   \n",
       "\n",
       "                                                                                                                      opposite_no_m  \\\n",
       "0  df22edbc0e3dd6bc4f2f453e687b743e8442a54834b64f06ee093a91c4a41385d46579107a443eee3ec0ce2339bcea4152a996ca8cf0b2cc846eb2acc9cc241b   \n",
       "1  df22edbc0e3dd6bc4f2f453e687b743e8442a54834b64f06ee093a91c4a41385d46579107a443eee3ec0ce2339bcea4152a996ca8cf0b2cc846eb2acc9cc241b   \n",
       "2  d73a454ed717461675c5a243fa7bab9892640643b4a0855fabf86231ec6d274df9511cc4aa305bfc80592d3c6b0d20446d7f1fd7e2ac0f0436f57c409469fd2a   \n",
       "3  d73a454ed717461675c5a243fa7bab9892640643b4a0855fabf86231ec6d274df9511cc4aa305bfc80592d3c6b0d20446d7f1fd7e2ac0f0436f57c409469fd2a   \n",
       "4  d73a454ed717461675c5a243fa7bab9892640643b4a0855fabf86231ec6d274df9511cc4aa305bfc80592d3c6b0d20446d7f1fd7e2ac0f0436f57c409469fd2a   \n",
       "\n",
       "   calltype_id    request_datetime  sms_day  sms_hour      sms_ts  \\\n",
       "0            2 2020-03-01 00:00:27        1         0  1583020827   \n",
       "1            2 2020-03-01 00:00:27        1         0  1583020827   \n",
       "2            1 2020-03-01 00:00:55        1         0  1583020855   \n",
       "3            1 2020-03-01 00:00:55        1         0  1583020855   \n",
       "4            1 2020-03-01 00:01:20        1         0  1583020880   \n",
       "\n",
       "   sms_day_count_max  sms_day_count_min  sms_day_count_mean  \\\n",
       "0                 74                  8           41.424424   \n",
       "1                 74                  8           41.424424   \n",
       "2                182                  1           78.667954   \n",
       "3                182                  1           78.667954   \n",
       "4                182                  1           78.667954   \n",
       "\n",
       "   sms_day_count_std  sms_hour_count_max  sms_hour_count_min  \\\n",
       "0          16.974697                 126                   1   \n",
       "1          16.974697                 126                   1   \n",
       "2          77.305629                 106                   1   \n",
       "3          77.305629                 106                   1   \n",
       "4          77.305629                 106                   1   \n",
       "\n",
       "   sms_hour_count_mean  sms_hour_count_std  sms_count  \n",
       "0            63.134134           30.640949        999  \n",
       "1            63.134134           30.640949        999  \n",
       "2            49.250965           33.940652        518  \n",
       "3            49.250965           33.940652        518  \n",
       "4            49.250965           33.940652        518  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sms.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 应用表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_app = pd.read_csv('raw_data/train/train_app.csv')\n",
    "test_app = pd.read_csv('raw_data/test/test_app.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 同样只取最后一个月\n",
    "\n",
    "train_app = train_app[train_app['month_id'] == '2020-03']\n",
    "train_app = train_app.reset_index(drop=True)\n",
    "\n",
    "df_app = pd.concat([train_app, test_app])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# 流量统计\n",
    "\n",
    "df_app['total_flow'] = df_app.groupby('phone_no_m')['flow'].transform('sum')\n",
    "df_app['flow_max'] = df_app.groupby('phone_no_m')['flow'].transform('max')\n",
    "df_app['flow_min'] = df_app.groupby('phone_no_m')['flow'].transform('min')\n",
    "df_app['flow_mean'] = df_app.groupby('phone_no_m')['flow'].transform('mean')\n",
    "df_app['flow_std'] = df_app.groupby('phone_no_m')['flow'].transform('std')\n",
    "\n",
    "# 应用数\n",
    "df_app['app_count'] = df_app.groupby('phone_no_m')['phone_no_m'].transform('count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>phone_no_m</th>\n",
       "      <th>busi_name</th>\n",
       "      <th>flow</th>\n",
       "      <th>month_id</th>\n",
       "      <th>total_flow</th>\n",
       "      <th>flow_max</th>\n",
       "      <th>flow_min</th>\n",
       "      <th>flow_mean</th>\n",
       "      <th>flow_std</th>\n",
       "      <th>app_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>229a0f324de3c54b4cc4c82dafbf78ee5874fe491e4d9a5a44c16f243922f1884e84ce9a910dfee5be7b1f8b2ae2784cc6ee1f65222f841a84acef2b1f11cac2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>125.060604</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>5189.21207</td>\n",
       "      <td>2000.995233</td>\n",
       "      <td>0.00074</td>\n",
       "      <td>55.797979</td>\n",
       "      <td>250.141054</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>229a0f324de3c54b4cc4c82dafbf78ee5874fe491e4d9a5a44c16f243922f1884e84ce9a910dfee5be7b1f8b2ae2784cc6ee1f65222f841a84acef2b1f11cac2</td>\n",
       "      <td>QQ</td>\n",
       "      <td>7.247598</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>5189.21207</td>\n",
       "      <td>2000.995233</td>\n",
       "      <td>0.00074</td>\n",
       "      <td>55.797979</td>\n",
       "      <td>250.141054</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>229a0f324de3c54b4cc4c82dafbf78ee5874fe491e4d9a5a44c16f243922f1884e84ce9a910dfee5be7b1f8b2ae2784cc6ee1f65222f841a84acef2b1f11cac2</td>\n",
       "      <td>旺信</td>\n",
       "      <td>0.008341</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>5189.21207</td>\n",
       "      <td>2000.995233</td>\n",
       "      <td>0.00074</td>\n",
       "      <td>55.797979</td>\n",
       "      <td>250.141054</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>229a0f324de3c54b4cc4c82dafbf78ee5874fe491e4d9a5a44c16f243922f1884e84ce9a910dfee5be7b1f8b2ae2784cc6ee1f65222f841a84acef2b1f11cac2</td>\n",
       "      <td>微信</td>\n",
       "      <td>584.027821</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>5189.21207</td>\n",
       "      <td>2000.995233</td>\n",
       "      <td>0.00074</td>\n",
       "      <td>55.797979</td>\n",
       "      <td>250.141054</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>229a0f324de3c54b4cc4c82dafbf78ee5874fe491e4d9a5a44c16f243922f1884e84ce9a910dfee5be7b1f8b2ae2784cc6ee1f65222f841a84acef2b1f11cac2</td>\n",
       "      <td>起点读书</td>\n",
       "      <td>5.342432</td>\n",
       "      <td>2020-03</td>\n",
       "      <td>5189.21207</td>\n",
       "      <td>2000.995233</td>\n",
       "      <td>0.00074</td>\n",
       "      <td>55.797979</td>\n",
       "      <td>250.141054</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                                         phone_no_m  \\\n",
       "0  229a0f324de3c54b4cc4c82dafbf78ee5874fe491e4d9a5a44c16f243922f1884e84ce9a910dfee5be7b1f8b2ae2784cc6ee1f65222f841a84acef2b1f11cac2   \n",
       "1  229a0f324de3c54b4cc4c82dafbf78ee5874fe491e4d9a5a44c16f243922f1884e84ce9a910dfee5be7b1f8b2ae2784cc6ee1f65222f841a84acef2b1f11cac2   \n",
       "2  229a0f324de3c54b4cc4c82dafbf78ee5874fe491e4d9a5a44c16f243922f1884e84ce9a910dfee5be7b1f8b2ae2784cc6ee1f65222f841a84acef2b1f11cac2   \n",
       "3  229a0f324de3c54b4cc4c82dafbf78ee5874fe491e4d9a5a44c16f243922f1884e84ce9a910dfee5be7b1f8b2ae2784cc6ee1f65222f841a84acef2b1f11cac2   \n",
       "4  229a0f324de3c54b4cc4c82dafbf78ee5874fe491e4d9a5a44c16f243922f1884e84ce9a910dfee5be7b1f8b2ae2784cc6ee1f65222f841a84acef2b1f11cac2   \n",
       "\n",
       "  busi_name        flow month_id  total_flow     flow_max  flow_min  \\\n",
       "0       NaN  125.060604  2020-03  5189.21207  2000.995233   0.00074   \n",
       "1        QQ    7.247598  2020-03  5189.21207  2000.995233   0.00074   \n",
       "2        旺信    0.008341  2020-03  5189.21207  2000.995233   0.00074   \n",
       "3        微信  584.027821  2020-03  5189.21207  2000.995233   0.00074   \n",
       "4      起点读书    5.342432  2020-03  5189.21207  2000.995233   0.00074   \n",
       "\n",
       "   flow_mean    flow_std  app_count  \n",
       "0  55.797979  250.141054         93  \n",
       "1  55.797979  250.141054         93  \n",
       "2  55.797979  250.141054         93  \n",
       "3  55.797979  250.141054         93  \n",
       "4  55.797979  250.141054         93  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_app.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((8151, 6), (985666, 20), (1319506, 16), (512100, 10))"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_user.shape, df_voc.shape, df_sms.shape, df_app.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_voc = df_voc[['phone_no_m', 'calltype_id', \n",
    "                 'call_dur', 'voc_day', 'voc_hour',\n",
    "                 'voc_day_count_max', 'voc_day_count_min', 'voc_day_count_mean', 'voc_day_count_std', \n",
    "                 'voc_hour_count_max', 'voc_hour_count_min', 'voc_hour_count_mean', 'voc_hour_count_std',\n",
    "                 'voc_count']].drop_duplicates(subset=['phone_no_m'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sms = df_sms[['phone_no_m', 'sms_day', 'sms_hour', \n",
    "                 'sms_day_count_max', 'sms_day_count_min', 'sms_day_count_mean', 'sms_day_count_std', \n",
    "                 'sms_hour_count_max', 'sms_hour_count_min', 'sms_hour_count_mean', 'sms_hour_count_std',\n",
    "                 'sms_count']].drop_duplicates(subset=['phone_no_m'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_app = df_app[['phone_no_m', 'total_flow', 'flow_max', 'flow_min',\n",
    "                 'flow_mean', 'flow_std', 'app_count']].drop_duplicates(subset=['phone_no_m'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>arpu_202004</th>\n",
       "      <th>city_name</th>\n",
       "      <th>county_name</th>\n",
       "      <th>idcard_cnt</th>\n",
       "      <th>label</th>\n",
       "      <th>phone_no_m</th>\n",
       "      <th>calltype_id</th>\n",
       "      <th>call_dur</th>\n",
       "      <th>voc_day</th>\n",
       "      <th>voc_hour</th>\n",
       "      <th>voc_day_count_max</th>\n",
       "      <th>voc_day_count_min</th>\n",
       "      <th>voc_day_count_mean</th>\n",
       "      <th>voc_day_count_std</th>\n",
       "      <th>voc_hour_count_max</th>\n",
       "      <th>voc_hour_count_min</th>\n",
       "      <th>voc_hour_count_mean</th>\n",
       "      <th>voc_hour_count_std</th>\n",
       "      <th>voc_count</th>\n",
       "      <th>sms_day</th>\n",
       "      <th>sms_hour</th>\n",
       "      <th>sms_day_count_max</th>\n",
       "      <th>sms_day_count_min</th>\n",
       "      <th>sms_day_count_mean</th>\n",
       "      <th>sms_day_count_std</th>\n",
       "      <th>sms_hour_count_max</th>\n",
       "      <th>sms_hour_count_min</th>\n",
       "      <th>sms_hour_count_mean</th>\n",
       "      <th>sms_hour_count_std</th>\n",
       "      <th>sms_count</th>\n",
       "      <th>total_flow</th>\n",
       "      <th>flow_max</th>\n",
       "      <th>flow_min</th>\n",
       "      <th>flow_mean</th>\n",
       "      <th>flow_std</th>\n",
       "      <th>app_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>16</td>\n",
       "      <td>97</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>672ddbf02a5544d32e4ecc9433b1981bffe23bf912273a3a835f6cccb78b8ed7554e9ab0fbcd33d19eb6063ce00542dd223cc5cc83c68f07bcf933547b6776b1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.971831</td>\n",
       "      <td>3.601475</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.042254</td>\n",
       "      <td>3.025778</td>\n",
       "      <td>71.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.089552</td>\n",
       "      <td>4.362283</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.059701</td>\n",
       "      <td>6.588622</td>\n",
       "      <td>134.0</td>\n",
       "      <td>168.124505</td>\n",
       "      <td>99.590643</td>\n",
       "      <td>0.000456</td>\n",
       "      <td>2.849568</td>\n",
       "      <td>13.173569</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>60.0</td>\n",
       "      <td>10</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5e1272273e041e82cb275ae877710be98cdaf5b0a8f34de8d361f71d5268fa0851edffd3950e170df1e3846fcf90cc7cc8299be9139a2ac4b5c5e5121d832674</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.363636</td>\n",
       "      <td>0.504525</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.727273</td>\n",
       "      <td>0.904534</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.142857</td>\n",
       "      <td>4.892586</td>\n",
       "      <td>71.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>41.112045</td>\n",
       "      <td>23.306550</td>\n",
       "      <td>357.0</td>\n",
       "      <td>10088.555221</td>\n",
       "      <td>2537.237967</td>\n",
       "      <td>0.001075</td>\n",
       "      <td>53.662528</td>\n",
       "      <td>237.544176</td>\n",
       "      <td>188.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>63.0</td>\n",
       "      <td>11</td>\n",
       "      <td>160</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>eaab3472ec87b076e69e6e8bb62b14341638fc63661a6c682d6add360a4332a8ad294d8470d64a73c6e53e8413f0ad93b9ea65afab717e58d312554f33553ee7</td>\n",
       "      <td>1.0</td>\n",
       "      <td>125.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.151515</td>\n",
       "      <td>5.496608</td>\n",
       "      <td>36.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22.818182</td>\n",
       "      <td>9.387167</td>\n",
       "      <td>264.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>12.783394</td>\n",
       "      <td>8.035024</td>\n",
       "      <td>44.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>25.397112</td>\n",
       "      <td>12.424601</td>\n",
       "      <td>277.0</td>\n",
       "      <td>357.531875</td>\n",
       "      <td>74.788056</td>\n",
       "      <td>0.000526</td>\n",
       "      <td>3.928922</td>\n",
       "      <td>12.155846</td>\n",
       "      <td>91.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>23.2</td>\n",
       "      <td>11</td>\n",
       "      <td>181</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0ce1bb415704178bf44e9c9b431a39b083a132c8e6d99f3627cd4520064b93ec66a72d085a67f0f40eebb9f901072965073e398f04c4ae500a1db4dfb13a972c</td>\n",
       "      <td>1.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>25.006885</td>\n",
       "      <td>8.531885</td>\n",
       "      <td>104.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>67.137694</td>\n",
       "      <td>28.927287</td>\n",
       "      <td>581.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.635452</td>\n",
       "      <td>7.869170</td>\n",
       "      <td>88.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>54.076923</td>\n",
       "      <td>21.127533</td>\n",
       "      <td>598.0</td>\n",
       "      <td>2.176389</td>\n",
       "      <td>1.118252</td>\n",
       "      <td>0.000553</td>\n",
       "      <td>0.435278</td>\n",
       "      <td>0.449543</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>50.0</td>\n",
       "      <td>10</td>\n",
       "      <td>82</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28b87f35f63f65096a53e3a4c97eaffd4a6c43ffa7e92d7706b20520aa11fff0f4ce06203f66758ec0f0e70780e4e258aae6aa1f23439e115528e08eda71ed20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.666667</td>\n",
       "      <td>0.516398</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.870588</td>\n",
       "      <td>3.750518</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9.352941</td>\n",
       "      <td>3.816337</td>\n",
       "      <td>85.0</td>\n",
       "      <td>3303.071922</td>\n",
       "      <td>1684.194232</td>\n",
       "      <td>0.000098</td>\n",
       "      <td>27.525599</td>\n",
       "      <td>168.147333</td>\n",
       "      <td>120.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   arpu_202004  city_name  county_name  idcard_cnt  label  \\\n",
       "0         45.0         16           97           1    0.0   \n",
       "1         60.0         10           82           1    0.0   \n",
       "2         63.0         11          160           2    0.0   \n",
       "3         23.2         11          181           2    0.0   \n",
       "4         50.0         10           82           1    0.0   \n",
       "\n",
       "                                                                                                                         phone_no_m  \\\n",
       "0  672ddbf02a5544d32e4ecc9433b1981bffe23bf912273a3a835f6cccb78b8ed7554e9ab0fbcd33d19eb6063ce00542dd223cc5cc83c68f07bcf933547b6776b1   \n",
       "1  5e1272273e041e82cb275ae877710be98cdaf5b0a8f34de8d361f71d5268fa0851edffd3950e170df1e3846fcf90cc7cc8299be9139a2ac4b5c5e5121d832674   \n",
       "2  eaab3472ec87b076e69e6e8bb62b14341638fc63661a6c682d6add360a4332a8ad294d8470d64a73c6e53e8413f0ad93b9ea65afab717e58d312554f33553ee7   \n",
       "3  0ce1bb415704178bf44e9c9b431a39b083a132c8e6d99f3627cd4520064b93ec66a72d085a67f0f40eebb9f901072965073e398f04c4ae500a1db4dfb13a972c   \n",
       "4  28b87f35f63f65096a53e3a4c97eaffd4a6c43ffa7e92d7706b20520aa11fff0f4ce06203f66758ec0f0e70780e4e258aae6aa1f23439e115528e08eda71ed20   \n",
       "\n",
       "   calltype_id  call_dur  voc_day  voc_hour  voc_day_count_max  \\\n",
       "0          1.0       1.0      1.0      10.0               12.0   \n",
       "1          2.0       1.0      6.0      10.0                2.0   \n",
       "2          1.0     125.0      1.0      15.0               25.0   \n",
       "3          1.0      29.0      1.0      13.0               37.0   \n",
       "4          1.0      65.0      4.0      18.0                1.0   \n",
       "\n",
       "   voc_day_count_min  voc_day_count_mean  voc_day_count_std  \\\n",
       "0                1.0            4.971831           3.601475   \n",
       "1                1.0            1.363636           0.504525   \n",
       "2                1.0           11.151515           5.496608   \n",
       "3                1.0           25.006885           8.531885   \n",
       "4                1.0            1.000000           0.000000   \n",
       "\n",
       "   voc_hour_count_max  voc_hour_count_min  voc_hour_count_mean  \\\n",
       "0                11.0                 1.0             6.042254   \n",
       "1                 3.0                 1.0             1.727273   \n",
       "2                36.0                 1.0            22.818182   \n",
       "3               104.0                 1.0            67.137694   \n",
       "4                 2.0                 1.0             1.666667   \n",
       "\n",
       "   voc_hour_count_std  voc_count  sms_day  sms_hour  sms_day_count_max  \\\n",
       "0            3.025778       71.0      1.0       7.0               16.0   \n",
       "1            0.904534       11.0      1.0       8.0               22.0   \n",
       "2            9.387167      264.0      1.0       9.0               32.0   \n",
       "3           28.927287      581.0      1.0       7.0               37.0   \n",
       "4            0.516398        6.0      1.0      11.0               13.0   \n",
       "\n",
       "   sms_day_count_min  sms_day_count_mean  sms_day_count_std  \\\n",
       "0                1.0            7.089552           4.362283   \n",
       "1                1.0           14.142857           4.892586   \n",
       "2                3.0           12.783394           8.035024   \n",
       "3                1.0           23.635452           7.869170   \n",
       "4                1.0            5.870588           3.750518   \n",
       "\n",
       "   sms_hour_count_max  sms_hour_count_min  sms_hour_count_mean  \\\n",
       "0                24.0                 1.0            12.059701   \n",
       "1                71.0                 1.0            41.112045   \n",
       "2                44.0                 1.0            25.397112   \n",
       "3                88.0                 2.0            54.076923   \n",
       "4                14.0                 1.0             9.352941   \n",
       "\n",
       "   sms_hour_count_std  sms_count    total_flow     flow_max  flow_min  \\\n",
       "0            6.588622      134.0    168.124505    99.590643  0.000456   \n",
       "1           23.306550      357.0  10088.555221  2537.237967  0.001075   \n",
       "2           12.424601      277.0    357.531875    74.788056  0.000526   \n",
       "3           21.127533      598.0      2.176389     1.118252  0.000553   \n",
       "4            3.816337       85.0   3303.071922  1684.194232  0.000098   \n",
       "\n",
       "   flow_mean    flow_std  app_count  \n",
       "0   2.849568   13.173569       59.0  \n",
       "1  53.662528  237.544176      188.0  \n",
       "2   3.928922   12.155846       91.0  \n",
       "3   0.435278    0.449543        5.0  \n",
       "4  27.525599  168.147333      120.0  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.merge(df_user, df_voc, how='left', on='phone_no_m')\n",
    "df = pd.merge(df, df_sms, how='left', on='phone_no_m')\n",
    "df = pd.merge(df, df_app, how='left', on='phone_no_m')\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((6106, 36), (2045, 36))"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train = df[df.label.notna()]\n",
    "df_test = df[df.label.isna()]\n",
    "\n",
    "df_train.shape, df_test.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 建模"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "import lightgbm as lgb\n",
    "from sklearn.model_selection import StratifiedKFold\n",
    "from sklearn.metrics import f1_score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Fold_1 Training ================================\n",
      "\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "[100]\ttrain's auc: 0.992324\tvalid's auc: 0.938446\n",
      "Early stopping, best iteration is:\n",
      "[124]\ttrain's auc: 0.994394\tvalid's auc: 0.939824\n",
      "\n",
      "Fold_2 Training ================================\n",
      "\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "[100]\ttrain's auc: 0.992926\tvalid's auc: 0.936448\n",
      "Early stopping, best iteration is:\n",
      "[57]\ttrain's auc: 0.983997\tvalid's auc: 0.939062\n",
      "\n",
      "Fold_3 Training ================================\n",
      "\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "[100]\ttrain's auc: 0.992105\tvalid's auc: 0.951032\n",
      "Early stopping, best iteration is:\n",
      "[123]\ttrain's auc: 0.994264\tvalid's auc: 0.951417\n",
      "\n",
      "Fold_4 Training ================================\n",
      "\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "[100]\ttrain's auc: 0.992364\tvalid's auc: 0.954768\n",
      "Early stopping, best iteration is:\n",
      "[149]\ttrain's auc: 0.995512\tvalid's auc: 0.956088\n",
      "\n",
      "Fold_5 Training ================================\n",
      "\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "Early stopping, best iteration is:\n",
      "[35]\ttrain's auc: 0.975046\tvalid's auc: 0.939638\n"
     ]
    }
   ],
   "source": [
    "ycol = 'label'\n",
    "feature_names = list(\n",
    "    filter(lambda x: x not in [ycol, 'phone_no_m'], df_train.columns))\n",
    "\n",
    "model = lgb.LGBMClassifier(objective='binary',\n",
    "                           boosting_type='gbdt',\n",
    "                           tree_learner='serial',\n",
    "                           num_leaves=64,\n",
    "                           max_depth=8,\n",
    "                           learning_rate=0.1,\n",
    "                           n_estimators=10000,\n",
    "                           subsample=0.8,\n",
    "                           feature_fraction=0.6,\n",
    "                           reg_alpha=10,\n",
    "                           reg_lambda=12,\n",
    "                           random_state=2020,\n",
    "                           is_unbalance=True,\n",
    "                           metric='auc')\n",
    "\n",
    "\n",
    "oof = []\n",
    "prediction = df_test[['phone_no_m']]\n",
    "prediction[ycol] = 0\n",
    "df_importance_list = []\n",
    "\n",
    "kfold = StratifiedKFold(n_splits=5, shuffle=True, random_state=2020)\n",
    "for fold_id, (trn_idx, val_idx) in enumerate(kfold.split(df_train[feature_names], df_train[ycol])):\n",
    "    X_train = df_train.iloc[trn_idx][feature_names]\n",
    "    Y_train = df_train.iloc[trn_idx][ycol]\n",
    "\n",
    "    X_val = df_train.iloc[val_idx][feature_names]\n",
    "    Y_val = df_train.iloc[val_idx][ycol]\n",
    "\n",
    "    print('\\nFold_{} Training ================================\\n'.format(fold_id+1))\n",
    "\n",
    "    lgb_model = model.fit(X_train,\n",
    "                          Y_train,\n",
    "                          eval_names=['train', 'valid'],\n",
    "                          eval_set=[(X_train, Y_train), (X_val, Y_val)],\n",
    "                          verbose=100,\n",
    "                          eval_metric='auc',\n",
    "                          early_stopping_rounds=50)\n",
    "\n",
    "    pred_val = lgb_model.predict(\n",
    "        X_val, num_iteration=lgb_model.best_iteration_)\n",
    "    df_oof = df_train.iloc[val_idx][['phone_no_m', ycol]].copy()\n",
    "    df_oof['pred'] = pred_val\n",
    "    oof.append(df_oof)\n",
    "\n",
    "    pred_test = lgb_model.predict(\n",
    "        df_test[feature_names], num_iteration=lgb_model.best_iteration_)\n",
    "    prediction[ycol] += pred_test / kfold.n_splits\n",
    "\n",
    "    df_importance = pd.DataFrame({\n",
    "        'column': feature_names,\n",
    "        'importance': lgb_model.feature_importances_,\n",
    "    })\n",
    "    df_importance_list.append(df_importance)\n",
    "\n",
    "    del lgb_model, pred_val, pred_test, X_train, Y_train, X_val, Y_val\n",
    "    gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>column</th>\n",
       "      <th>importance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>arpu_202004</td>\n",
       "      <td>201.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>call_dur</td>\n",
       "      <td>173.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>voc_day_count_mean</td>\n",
       "      <td>161.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>sms_day_count_mean</td>\n",
       "      <td>159.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>county_name</td>\n",
       "      <td>159.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>app_count</td>\n",
       "      <td>149.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>flow_min</td>\n",
       "      <td>137.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>voc_day_count_std</td>\n",
       "      <td>128.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>sms_day_count_std</td>\n",
       "      <td>121.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>flow_mean</td>\n",
       "      <td>115.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>sms_hour_count_mean</td>\n",
       "      <td>111.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>sms_count</td>\n",
       "      <td>108.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>voc_hour_count_std</td>\n",
       "      <td>106.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>total_flow</td>\n",
       "      <td>106.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>voc_count</td>\n",
       "      <td>103.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>city_name</td>\n",
       "      <td>103.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>sms_hour_count_std</td>\n",
       "      <td>101.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>sms_hour</td>\n",
       "      <td>96.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>flow_max</td>\n",
       "      <td>93.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>sms_hour_count_max</td>\n",
       "      <td>89.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>voc_hour_count_mean</td>\n",
       "      <td>83.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>flow_std</td>\n",
       "      <td>82.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>voc_hour</td>\n",
       "      <td>67.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>idcard_cnt</td>\n",
       "      <td>57.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>sms_day_count_max</td>\n",
       "      <td>42.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>voc_day_count_max</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>sms_day_count_min</td>\n",
       "      <td>31.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>sms_day</td>\n",
       "      <td>30.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>voc_hour_count_max</td>\n",
       "      <td>25.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>voc_day_count_min</td>\n",
       "      <td>24.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>voc_day</td>\n",
       "      <td>23.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>calltype_id</td>\n",
       "      <td>21.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>voc_hour_count_min</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>sms_hour_count_min</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 column  importance\n",
       "0           arpu_202004       201.8\n",
       "1              call_dur       173.4\n",
       "2    voc_day_count_mean       161.4\n",
       "3    sms_day_count_mean       159.4\n",
       "4           county_name       159.0\n",
       "5             app_count       149.4\n",
       "6              flow_min       137.4\n",
       "7     voc_day_count_std       128.4\n",
       "8     sms_day_count_std       121.0\n",
       "9             flow_mean       115.0\n",
       "10  sms_hour_count_mean       111.4\n",
       "11            sms_count       108.8\n",
       "12   voc_hour_count_std       106.8\n",
       "13           total_flow       106.0\n",
       "14            voc_count       103.4\n",
       "15            city_name       103.0\n",
       "16   sms_hour_count_std       101.6\n",
       "17             sms_hour        96.6\n",
       "18             flow_max        93.6\n",
       "19   sms_hour_count_max        89.4\n",
       "20  voc_hour_count_mean        83.4\n",
       "21             flow_std        82.4\n",
       "22             voc_hour        67.4\n",
       "23           idcard_cnt        57.6\n",
       "24    sms_day_count_max        42.4\n",
       "25    voc_day_count_max        42.0\n",
       "26    sms_day_count_min        31.4\n",
       "27              sms_day        30.4\n",
       "28   voc_hour_count_max        25.2\n",
       "29    voc_day_count_min        24.6\n",
       "30              voc_day        23.6\n",
       "31          calltype_id        21.2\n",
       "32   voc_hour_count_min        14.0\n",
       "33   sms_hour_count_min         4.0"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_importance = pd.concat(df_importance_list)\n",
    "df_importance = df_importance.groupby(['column'])['importance'].agg(\n",
    "    'mean').sort_values(ascending=False).reset_index()\n",
    "df_importance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "f1: 0.8765934363981557\n"
     ]
    }
   ],
   "source": [
    "df_oof = pd.concat(oof)\n",
    "f1 = f1_score(df_oof[ycol], df_oof['pred'])\n",
    "print('f1:', f1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>phone_no_m</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>6106</td>\n",
       "      <td>22d522340df77e2252c1a4d92b4bcb00d515e36f3ec6bf94e017b4ffd67e26520af18637ad91bde69abd831cc36cdc0c5af5e57bc280f95f79efef7bfa9c6926</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6107</td>\n",
       "      <td>5220d4b8429bdba3971a7b46a088c6b8fa6710f40607598ac1219daab6071510b3f66f78637420574dbb2d5488d44de9d4360a644bbe4ec45aaa4b7513bdc886</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6108</td>\n",
       "      <td>7d19dd2b50ced56f03d23bf928cf34dc570a48525571a868ffdcdc53a865b7a41c386a6f1d6567dd4a710530b933308c77d1ef89f77b580d1983dadd3de162ea</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6109</td>\n",
       "      <td>134a4a591185c9d3788021896dcfc235e9e0a6a1e3f8a4fd3a9f9d9bf4a033e791b7199db980b0678020bdf689d719306a5c694ffa47b4ba2bec43ceda68dfc1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6110</td>\n",
       "      <td>2356dcd6759d50455ddaeed03c838843558e9182d5962f8a4c81a9a178063c49ab988750f795bb6c5462aee5dace2ad9fd18dbb1a6619dbb1e771e9b0f192da9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                                            phone_no_m  \\\n",
       "6106  22d522340df77e2252c1a4d92b4bcb00d515e36f3ec6bf94e017b4ffd67e26520af18637ad91bde69abd831cc36cdc0c5af5e57bc280f95f79efef7bfa9c6926   \n",
       "6107  5220d4b8429bdba3971a7b46a088c6b8fa6710f40607598ac1219daab6071510b3f66f78637420574dbb2d5488d44de9d4360a644bbe4ec45aaa4b7513bdc886   \n",
       "6108  7d19dd2b50ced56f03d23bf928cf34dc570a48525571a868ffdcdc53a865b7a41c386a6f1d6567dd4a710530b933308c77d1ef89f77b580d1983dadd3de162ea   \n",
       "6109  134a4a591185c9d3788021896dcfc235e9e0a6a1e3f8a4fd3a9f9d9bf4a033e791b7199db980b0678020bdf689d719306a5c694ffa47b4ba2bec43ceda68dfc1   \n",
       "6110  2356dcd6759d50455ddaeed03c838843558e9182d5962f8a4c81a9a178063c49ab988750f795bb6c5462aee5dace2ad9fd18dbb1a6619dbb1e771e9b0f192da9   \n",
       "\n",
       "      label  \n",
       "6106      0  \n",
       "6107      0  \n",
       "6108      0  \n",
       "6109      0  \n",
       "6110      0  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sub = prediction.copy(deep=True)\n",
    "sub['label'] = sub['label'].apply(lambda x: 1 if x>0 else 0)\n",
    "sub.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1543\n",
       "1     502\n",
       "Name: label, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sub.label.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "sub.to_csv('submissions/baseline_{}.csv'.format(f1), index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
